/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.foxline.clinicplus.service;

import br.com.foxline.clinicplus.entity.*;
import br.com.foxline.clinicplus.enums.*;
import br.com.foxline.clinicplus.util.VOReportDiary;
import java.io.Serializable;
import java.math.BigDecimal;
import java.net.URL;
import java.util.*;
import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.persistence.Query;

/**
 *
 * @author roniere
 */
@Stateless
public class DiaryService extends GenericService<Diary> implements Serializable {

    @EJB
    SystemUserService systemUserService;
    @EJB
    CustomerService customerService;
    @EJB
    ClinicService clinicService;

    public DiaryService() {
        super(Diary.class);
    }

    public List<Diary> getDiaryForProcedure(Procedure procedure, Date dateInitial, Date dateFinal) {
        String sql = "Select d from Diary d JOIN d.procedure p where ";

        if (procedure.getId() != null) {
            sql += " p.id = :procedure and ";
        }
        sql += " d.startTime BETWEEN :datei AND :datef order by d.id asc";

        Query createQuery = getEntityManager().createQuery(sql);

        if (procedure.getId() != null) {
            createQuery.setParameter("procedure", procedure.getId());
        }

        createQuery.setParameter("datei", dateInitial);
        createQuery.setParameter("datef", dateFinal);

        //createQuery.setParameter("diarySituation", DiarySituation.CALL_WAITING);
        //createQuery.setParameter("status", true);
        return createQuery.getResultList();
    }

    public Diary getLastDiary(Customer customer) {
        String sql = "Select d from Diary d JOIN d.customer c "
                + " where c.id = :customer ";
        Query createQuery = getEntityManager().createQuery(sql).setMaxResults(1);
        createQuery.setParameter("customer", customer.getId());

        try {
            return (Diary) createQuery.getResultList().get(0);
        } catch (Exception e) {
            return null;
        }

    }

    public List<Diary> findDiaryCustomer(Customer customer, String... order) {
        String sql = "Select d from Diary d JOIN d.customer p where";

        if (customer.getName() != null && !customer.getName().isEmpty()) {
            sql += "  lower(p.name) like lower(:customer) and ";
        }

        if (customer.getCpf() != null && !customer.getCpf().isEmpty()) {
            sql += "  lower(p.cpf) like lower(:cpf) and ";
        }

        if (customer.getId() != null) {
            sql += " p.id = :id and ";
        }

        sql += " 1 = 1";
        try {
            sql += " order by d.id " + order[0];
        } catch (Exception e) {
            sql += " order by d.id asc";
        }

        Query createQuery = getEntityManager().createQuery(sql);
        if (customer.getName() != null && !customer.getName().isEmpty()) {
            createQuery.setParameter("customer", "%" + customer.getName() + "%");
        }
        if (customer.getId() != null) {
            createQuery.setParameter("id", customer.getId());
        }

        if (customer.getCpf() != null && !customer.getCpf().isEmpty()) {
            createQuery.setParameter("cpf", customer.getCpf());
        }
        return createQuery.getResultList();
    }

    public List<Diary> getDiaryCustomerForDate(Customer customer, Date dateInitial, Date dateFinal) {
        String sql = "Select d from Diary d JOIN d.customer p "
                + " where p.id = :customer"
                + " and d.startTime BETWEEN :dateInitial AND :dateFinal "
                + " order by d.id desc";
        Query createQuery = getEntityManager().createQuery(sql);
        createQuery.setParameter("customer", customer.getId());
        createQuery.setParameter("dateInitial", dateInitial);
        createQuery.setParameter("dateFinal", dateFinal);
        //createQuery.setParameter("status", true);
//        System.err.println("Result Customer:" + createQuery.getResultList().size());
        return createQuery.getResultList();
    }

    public List<Diary> getDiaryForDoctor(Doctor doctor, Date dateInitial, Date dateFinal) {
        String sql = "Select d from Diary d JOIN d.doctor p where ";

        if (doctor.getId() != null) {
            sql += " p.id = :doctor and ";
        }
        sql += " d.startTime BETWEEN :datei AND :datef"
             + " order by d.id asc";
        Query createQuery = getEntityManager().createQuery(sql);

        if (doctor.getId() != null) {
            createQuery.setParameter("doctor", doctor.getId());
        }
        createQuery.setParameter("datei", dateInitial);
        createQuery.setParameter("datef", dateFinal);

        return createQuery.getResultList();
    }

    @Override
    public List<Diary> findAll() {
        return getEntityManager().createQuery("select e from Diary e where e.status = true ").getResultList();
    }

    public String converte(String text) {


        return text.replaceAll("-", "").replaceAll("[ãâàáä]", "a").replaceAll("[êèéë]", "e").replaceAll("[îìíï]", "i").replaceAll("[õôòóö]", "o").replaceAll("[ûúùü]", "u").replaceAll("[ÃÂÀÁÄ]", "A").replaceAll("[ÊÈÉË]", "E").replaceAll("[ÎÌÍÏ]", "I").replaceAll("[ÕÔÒÓÖ]", "O").replaceAll("[ÛÙÚÜ]", "U").replace('ç', 'c').replace('Ç', 'C').replace('ñ', 'n').replace('Ñ', 'N').replaceAll("!", "").replaceAll("\\[\\´\\`\\?!\\@\\#\\$\\%\\¨\\*", " ").replaceAll("\\(\\)\\=\\{\\}\\[\\]\\~\\^\\]", " ").replaceAll("[\\.\\;\\-\\_\\+\\'\\ª\\º\\:\\;\\/]", " ");

    }

    public void reportsDiaryExame(Diary diary, List<Diary> diarys) {
        HashMap<String, Object> parametros;
        parametros = new HashMap<String, Object>();

        parametros.put("codatendimento", diarys != null && diarys.size() > 1 ? null : diary.getId());
        parametros.put("procedure", diary.getProcedure() != null ? diary.getProcedure().getName() : "");
        parametros.put("codebar", (diary.getCustomer() != null ? (diary.getCustomer().getName() != null ? converte(diary.getCustomer().getName().toUpperCase()) : "") : ""));
        parametros.put("sexo", diary.getCustomer() != null ? diary.getCustomer().getGender() != null ? diary.getCustomer().getGender().toString() : "" : "");
        parametros.put("idade", diary.getCustomer() != null ? diary.getCustomer().getDateBirth() != null ? getAge(diary.getCustomer().getDateBirth()) + "" : "" : "");
        parametros.put("medico", diary.getDoctor() != null ? diary.getDoctor().getName() : "");
        parametros.put("medicoSolicitante", diary.getDoctorRequest() != null ? diary.getDoctorRequest().getName() : "");
        parametros.put("systemuser", systemUserService.getCurrentUser() != null ? systemUserService.getCurrentUser().getName() : "");


        if (diary.getCustomer() != null) {

            diary.setCustomer(customerService.find(diary.getCustomer().getId()));

            parametros.put("name", diary.getCustomer().getName());
            parametros.put("cpf", diary.getCustomer().getCpf());
            parametros.put("rg", diary.getCustomer().getRg());
            parametros.put("gender.name", diary.getCustomer().getGender() != null ? diary.getCustomer().getGender().getName() : "");
            parametros.put("phone", diary.getCustomer().getPhone());
            parametros.put("cellular", diary.getCustomer().getCellular());
            parametros.put("dateBirth", diary.getCustomer().getDateBirth());
            parametros.put("address.publicPark", diary.getCustomer().getAddress() != null ? diary.getCustomer().getAddress().getPublicPark() : "");
            parametros.put("address.number", diary.getCustomer().getAddress() != null ? diary.getCustomer().getAddress().getNumber() : "");
            parametros.put("address.neighborhood", diary.getCustomer().getAddress() != null ? diary.getCustomer().getAddress().getNeighborhood() : "");
            parametros.put("address.zipcode", diary.getCustomer().getAddress() != null ? diary.getCustomer().getAddress().getZipcode() : "");
            parametros.put("address.city.name", diary.getCustomer().getAddress() != null ? diary.getCustomer().getAddress().getCity() != null ? diary.getCustomer().getAddress().getCity().getName() : "" : "");
            parametros.put("cardNumber", diary.getCustomer().getCardNumber());
            parametros.put("id", diary.getCustomer().getId());

        }


        parametros.put("operator.name", diary.getOperator() != null ? diary.getOperator().getName() : "");

        URL subreport = Thread.currentThread().getContextClassLoader().getResource(""
                + "br/com/foxline/clinicplus/reports/DiaryExame_subreport.jasper");

        parametros.put("SUBREPORT_DIR",
                subreport.toString().replace("DiaryExame_subreport.jasper" + "", "").replace("file:", ""));

//        System.err.println(parametros.get("SUBREPORT_DIR"));

        VOReportDiary vOReportDiary = new VOReportDiary();
        vOReportDiary.setDiarys(diarys);

        List<VOReportDiary> vo = new ArrayList<VOReportDiary>();
        vo.add(vOReportDiary);


        parametros = clinicService.getCabecalho(parametros);
        ReportService.genReport(parametros, vo, Reports.DIARYEXAME);
    }

    public int getAge(Date data) {
        int idade = 0;
        if (data != null) {
            Calendar c = new GregorianCalendar();
            Calendar dateBirth = new GregorianCalendar();
            dateBirth.setTime(data);

            idade = c.get(Calendar.YEAR) - dateBirth.get(Calendar.YEAR);

            dateBirth.add(Calendar.YEAR, idade);

            if (c.before(dateBirth)) {
                idade--;
            }
        }
        return idade;

    }

    public List<Diary> loadReturnDiaryCustomer(Customer customer) {
        String sql = "Select d from Diary d JOIN d.customer c "
                + " where c.id = :customer"
                + " order by d.id desc";
        Query createQuery = getEntityManager().createQuery(sql);
        createQuery.setParameter("customer", customer.getId());
        return createQuery.getResultList();
    }

    public Long countDiaryForDate(Date dateInitial, Date dateFinal, TypeProcedure typeProcedure) {

        String sql = "Select count(i) from Income i JOIN i.diary d JOIN d.procedure p where "
                + " i.createAt BETWEEN :datei AND :datef "
                + " AND p.typeProcedure = :typeProcedure ";

        // SCHEDULED("Agendado"), CONFIRMED("Confirmado"), CANCEL("Cancelado"), 
        //CALL_WAITING("Aguardando Atendimento"), CALL_ENDED("Atendimento Realizado");

        Query createQuery = getEntityManager().createQuery(sql);
        createQuery.setParameter("typeProcedure", typeProcedure);
        createQuery.setParameter("datei", dateInitial);
        createQuery.setParameter("datef", dateFinal);
        try {
            return (Long) createQuery.getResultList().get(0);
        } catch (Exception e) {
            return 0L;
        }
    }

    public Long getValueIncomeGeneric(Income income, Procedure procedure,
            Doctor doctor, Date dateInitial, Date dateFinal, PaymentType paymentType, SystemUser systemUser,
            TypeIncome typeIncome, TypeDebt typeDebt, List<Procedure> procedures,
            Operator operator, List<Operator> operators, TypeCredit typeCredit, TypeProcedure typeProcedure, GroupProcedure groupProcedure) {

        String sql = "Select COUNT(i.id) from Income i ";
        if (((procedure != null && procedure.getId() != null)
                || (procedures != null && !procedures.isEmpty()))
                || (typeProcedure != null)
                || (groupProcedure != null)) {
            sql += " JOIN i.procedure p ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " JOIN i.doctor d ";
        }
        if (systemUser != null && systemUser.getId() != null) {
            sql += " JOIN i.systemUser su ";
        }
        sql += " JOIN i.diary di";
        if ((operator != null && operator.getId() != null) || (operators != null && !operators.isEmpty())) {
            sql += " JOIN di.operator op ";
        }

        sql += " where i.ativo = true ";
        if (operator != null && operator.getId() != null) {
            sql += " and op.id = :operator ";
        }
        if (operators != null && !operators.isEmpty()) {
            sql += " and op.id IN :operators ";
        }
        if (procedures != null && !procedures.isEmpty()) {
            sql += " and p.id IN :procedures";
        }
        if (paymentType != null) {
            sql += " and i.paymentType = :pt";
        }

        if (procedure != null && procedure.getId() != null) {
            sql += " and p.id = :procedure ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " and d.id = :doctor ";
        }

        if (dateInitial != null) {
            sql += " and i.createAt BETWEEN :dateInitial AND :dateFinal";
        }

        if (systemUser != null && systemUser.getId() != null) {
            sql += " and su.id = :systemuser";
        }
        if (typeIncome != null) {
            if (typeIncome == TypeIncome.C) {
                sql += " and ( i.typeIncome = :typeIncome or i.typeIncome is null) ";
            } else {
                sql += " and i.typeIncome = :typeIncome";
            }
        }
        if (typeDebt != null) {
            sql += " and i.typeDebt = :typeDebt";
        }

        if (typeCredit != null) {
            sql += " and i.typeCredit = :typeCredit";
        }

        if (typeProcedure != null) {
            sql += " and p.typeProcedure = :typeProcedure";
        }
        if (groupProcedure != null) {
            sql += " and p.groupProcedure = :groupProcedure";
        }


        Query createQuery = getEntityManager().createQuery(sql);

        if (procedure != null && procedure.getId() != null) {
            createQuery.setParameter("procedure", procedure.getId());
        }
        if (doctor != null && doctor.getId() != null) {
            createQuery.setParameter("doctor", doctor.getId());
        }
        if (dateInitial != null) {
            Calendar gc1 = GregorianCalendar.getInstance();
            gc1.setTime(dateInitial);
            gc1.set(GregorianCalendar.HOUR_OF_DAY, 0);


            Calendar gc2 = GregorianCalendar.getInstance();
            if (dateFinal == null) {
                gc2.setTime(dateInitial);
                gc2.add(GregorianCalendar.DAY_OF_MONTH, 1);
                gc2.set(GregorianCalendar.HOUR_OF_DAY, 0);
            } else {
                gc2.setTime(dateFinal);
                gc2.set(GregorianCalendar.HOUR_OF_DAY, 23);
                gc2.set(GregorianCalendar.MINUTE, 59);
                gc2.set(GregorianCalendar.SECOND, 0);
            }

            dateInitial = gc1.getTime();
            dateFinal = gc2.getTime();

            createQuery.setParameter("dateInitial", dateInitial);
            createQuery.setParameter("dateFinal", dateFinal);
        }
        if (paymentType != null) {
            createQuery.setParameter("pt", paymentType);
        }
        if (systemUser != null && systemUser.getId() != null) {
            createQuery.setParameter("systemuser", systemUser.getId());
        }
        if (typeIncome != null) {
            createQuery.setParameter("typeIncome", typeIncome);
        }
        if (typeDebt != null) {
            createQuery.setParameter("typeDebt", typeDebt);
        }
        if (typeCredit != null) {
            createQuery.setParameter("typeCredit", typeCredit);
        }
        if (typeProcedure != null) {
            createQuery.setParameter("typeProcedure", typeProcedure);
        }
        if (groupProcedure != null) {
            createQuery.setParameter("groupProcedure", groupProcedure);
        }

        if (operators != null && !operators.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Operator operator1 : operators) {
                list.add(operator1.getId());
            }
            createQuery.setParameter("operators", list);
        }
        if (procedures != null && !procedures.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Procedure procedure1 : procedures) {
                list.add(procedure1.getId());
            }
            createQuery.setParameter("procedures", list);
        }

        if (operator != null && operator.getId() != null) {
            createQuery.setParameter("operator", operator.getId());
        }

        System.err.println(sql);
        System.err.println(typeProcedure + "" + dateInitial + " - " + dateFinal);
        System.err.println((Long) createQuery.getSingleResult());

        return (Long) createQuery.getSingleResult();
    }

    public Long countDiary(TypeProcedure typeProcedure) {
        String sql = "Select count(d) from Diary d JOIN d.procedure p where "
                + " p.typeProcedure = :typeProcedure ";

        Query createQuery = getEntityManager().createQuery(sql);
        createQuery.setParameter("typeProcedure", typeProcedure);

        try {
            return (Long) createQuery.getResultList().get(0);
        } catch (Exception e) {
            return 0L;
        }
    }

    public BigDecimal getValueDiaryForDateToChart(Date dateInitial, Date dateFinal, PaymentType paymentType,
            TypeIncome typeIncome, TypeProcedure typeProcedure) {

        String sql = "Select sum(i.amountPaid) from Income i  JOIN i.diary di ";

        if (typeProcedure != null) {
            sql += " JOIN i.procedure p ";
        }

        sql += " where 1=1 ";

        if (paymentType != null) {
            sql += " and i.paymentType = :pt";
        }

        if (dateInitial != null) {
            sql += " and di.startTime BETWEEN :dateInitial AND :dateFinal";
        }

        if (typeIncome != null) {
            if (typeIncome == TypeIncome.C) {
                sql += " and ( i.typeIncome = :typeIncome or i.typeIncome is null) ";
            } else {
                sql += " and i.typeIncome = :typeIncome";
            }
        }

        if (typeProcedure != null) {
            sql += " and p.typeProcedure = :typeProcedure";
        }


        Query createQuery = getEntityManager().createQuery(sql);

        if (dateInitial != null) {
            Calendar gc1 = GregorianCalendar.getInstance();
            gc1.setTime(dateInitial);
            gc1.set(GregorianCalendar.HOUR_OF_DAY, 0);

            Calendar gc2 = GregorianCalendar.getInstance();
            if (dateFinal == null) {
                gc2.setTime(dateInitial);
                gc2.add(GregorianCalendar.DAY_OF_MONTH, 1);
                gc2.set(GregorianCalendar.HOUR_OF_DAY, 0);
            } else {
                gc2.setTime(dateFinal);
                gc2.set(GregorianCalendar.HOUR_OF_DAY, 23);
                gc2.set(GregorianCalendar.MINUTE, 59);
                gc2.set(GregorianCalendar.SECOND, 0);
            }
            dateInitial = gc1.getTime();
            dateFinal = gc2.getTime();
            createQuery.setParameter("dateInitial", dateInitial);
            createQuery.setParameter("dateFinal", dateFinal);
        }
        if (paymentType != null) {
            createQuery.setParameter("pt", paymentType);
        }

        if (typeIncome != null) {
            createQuery.setParameter("typeIncome", typeIncome);
        }

        if (typeProcedure != null) {
            createQuery.setParameter("typeProcedure", typeProcedure);
        }



        return (BigDecimal) createQuery.getSingleResult();
    }
}
